package cn.stylefeng.guns.sys.modular.system.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.core.util.DateUtil;
import cn.stylefeng.guns.sys.core.util.ExcelUtil;
import cn.stylefeng.guns.sys.core.util.FileUtils;
import cn.stylefeng.guns.sys.modular.system.entity.Mdzx;
import cn.stylefeng.guns.sys.modular.system.entity.ZxTzqk;
import cn.stylefeng.guns.sys.modular.system.mapper.ZxKemuMapper;
import cn.stylefeng.guns.sys.modular.system.mapper.ZxTzqkMapper;
import cn.stylefeng.guns.sys.modular.system.model.params.MdzxParam;
import cn.stylefeng.guns.sys.modular.system.model.params.ZxChengbenMingxiParam;
import cn.stylefeng.guns.sys.modular.system.model.params.ZxTzqkParam;
import cn.stylefeng.guns.sys.modular.system.model.result.MdzxResult;
import cn.stylefeng.guns.sys.modular.system.model.result.ZxChengbenMingxiResult;
import cn.stylefeng.guns.sys.modular.system.model.result.ZxTzqkResult;
import cn.stylefeng.guns.sys.modular.system.model.result.ZxTzqkViewResult;
import cn.stylefeng.guns.sys.modular.system.service.MdzxService;
import cn.stylefeng.guns.sys.modular.system.service.ZxChengbenMingxiService;
import cn.stylefeng.guns.sys.modular.system.service.ZxTzqkService;
import cn.stylefeng.guns.sys.modular.system.service.ZxYusuanService;
import cn.stylefeng.roses.core.reqres.response.ErrorResponseData;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.reqres.response.SuccessResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author jiangge
 * @since 2020-11-20
 */
@Service
public class ZxTzqkServiceImpl extends ServiceImpl<ZxTzqkMapper, ZxTzqk> implements ZxTzqkService {

    @Resource
    private MdzxService mdzxService;
    @Resource
    private ZxKemuMapper zxKemuMapper;
    @Resource
    private ZxTzqkMapper zxTzqkMapper;
    @Resource
    private ZxChengbenMingxiService zxChengbenMingxiService;
    @Resource
    private ZxYusuanService zxyusuanService;
    @Resource
    private TransService transService;

    @Override
    public void add(ZxTzqkParam param) {
        ZxTzqk entity = getEntity(param);
        entity.setCreator(LoginContextHolder.getContext().getUserId() + "");
        entity.setCreatorView(LoginContextHolder.getContext().getUser().getName());
        this.save(entity);
    }

    @Override
    public void delete(ZxTzqkParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(ZxTzqkParam param) {
        ZxTzqk oldEntity = getOldEntity(param);
        ZxTzqk newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        newEntity.setModifyBy(LoginContextHolder.getContext().getUserId() + "");
        newEntity.setModifyByView(LoginContextHolder.getContext().getUser().getName());
        newEntity.setModifyTime(new Date());
        this.updateById(newEntity);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void editTzqk(ZxTzqkParam param) {
        //总计
        if (param.getFirstLevelName() != null && "总计".equals(param.getFirstLevelName())) {
            MdzxParam mdzxParam = new MdzxParam();
            mdzxParam.setBtyZxMdzxId(param.getBtyZxMdzxId());
            mdzxParam.setChengbenHeji(param.getJine());
            mdzxService.updateByParam(mdzxParam);
        }
        //总投资
        else if (param.getSecondLevelName() != null && "总投资".equals(param.getSecondLevelName())) {
            MdzxParam mdzxParam = new MdzxParam();
            mdzxParam.setBtyZxMdzxId(param.getBtyZxMdzxId());
            mdzxParam.setYusuanHeji(param.getJine());
            mdzxService.updateByParam(mdzxParam);
        }
        //投资剩余
        else if (param.getSecondLevelName() != null && "投资剩余".equals(param.getSecondLevelName())) {
            MdzxParam mdzxParam = new MdzxParam();
            mdzxParam.setBtyZxMdzxId(param.getBtyZxMdzxId());
            mdzxParam.setTouzishengyu(param.getJine());
            mdzxService.updateByParam(mdzxParam);
        } else {
            //编辑的是一级或二级内容时
            ZxTzqkResult zxTzqkResult = selectTzqkByParam(param);
            if (zxTzqkResult == null) {
                //新增
                this.add(param);
            } else {
                //更新-1.备注和备忘都是空，金额是0时，删除对应数据 2.备注和备忘存在数据，更新数据
                if (StringUtils.isEmpty(param.getContent()) && StringUtils.isEmpty(param.getBeiwang()) && (param.getJine() == null || param.getJine().compareTo(BigDecimal.ZERO) == 0)) {
                    //备注和备忘都是空，删除对应tzqk记录
                    this.removeById(zxTzqkResult.getBtyZxTzqkId());
                } else {
                    ZxTzqk oldEntity = this.getById(zxTzqkResult.getBtyZxTzqkId());
                    ZxTzqk newEntity = getEntity(param);
                    ToolUtil.copyProperties(newEntity, oldEntity);
                    newEntity.setBtyZxTzqkId(zxTzqkResult.getBtyZxTzqkId());
                    newEntity.setModifyBy(LoginContextHolder.getContext().getUserId() + "");
                    newEntity.setModifyByView(LoginContextHolder.getContext().getUser().getName());
                    newEntity.setModifyTime(new Date());
                    this.updateById(newEntity);
                }
            }
        }

        updateJineHeJi(param.getBtyZxMdzxId());


    }

    @Override
    public ZxTzqkResult findBySpec(ZxTzqkParam param) {
        return null;
    }

    @Override
    public List<ZxTzqkResult> findListBySpec(ZxTzqkParam param) {
        return null;
    }

    @Override
    public LayuiPageInfo findPageBySpec(ZxTzqkParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public ZxTzqk selectOneByParam(ZxTzqkParam param) {
        QueryWrapper<ZxTzqk> wrapper = new QueryWrapper<>();
        wrapper.eq("bty_zx_mdzx_id", param.getBtyZxMdzxId());
        wrapper.eq("second_level_id", param.getSecondLevelId());
        ZxTzqk zxTzqk = this.baseMapper.selectOne(wrapper);

        return zxTzqk;
    }

    @Override
    public ZxTzqkResult selectTzqkByParam(ZxTzqkParam param) {
        List<ZxTzqkResult> zxTzqkResultList = this.baseMapper.selectTzqkByParam(param);
        if (CollectionUtil.isNotEmpty(zxTzqkResultList)) {
            if(zxTzqkResultList.size()==1){
                return zxTzqkResultList.get(0);
            }else {
                //重新添加
                this.baseMapper.deleteByMdzxAndKemu(param);
                return null;
            }
        }
        return null;
    }


    private Workbook getWorkBook(InputStream inputStream) throws IOException {
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);
            return wb;

        } catch (Exception e) {
            wb = new HSSFWorkbook(inputStream);
            return wb;
        }

    }


    /**
     * 投资情况导入
     *
     * @param fileName
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData batchImport(String fileName, MultipartFile file) throws Exception {

        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = getWorkBook(is);

        //获取所有的门店装修的门店信息
        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setContainsCopy("no");
        List<MdzxResult> mdzxResultList = mdzxService.customList(mdzxParam);
        Map<String, Long> mdzxMap = mdzxResultList.stream().collect(Collectors.toMap(MdzxResult::getBtyBrandIdView, MdzxResult::getBtyZxMdzxId));


        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        List<ZxTzqk> tzqkList = new ArrayList<>();
        Sheet sheet = wb.getSheetAt(0);
        //校验店是否存在
        Row rowFirst = sheet.getRow(2);
        String dianMing = rowFirst.getCell(1).getStringCellValue().trim();

        if (ToolUtil.isEmpty(dianMing)) {
            return new ErrorResponseData("门店信息不允许为空");
        }

        //工程项目中已经存在，门店装修中不存在，自动创建门店装修信息
        if (mdzxMap.get(dianMing) == null) {
            return new ErrorResponseData("导入失败，门店：" + dianMing + "，在装修门店中不存在，请先添加装修门店信息。");
        }

        //获取所有的一级科目-全
        List<ZxTzqkViewResult> zxKemuResultList1 = zxKemuMapper.selectAllYijiKemu("1");
        Map<String, Long> yijiKeMuMap = zxKemuResultList1.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getFirstLevelId));
        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        Map<String, Long> erjiKeMuMap = zxKemuResultList2.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getSecondLevelId));


        int lastRowNum = sheet.getLastRowNum();
        //循环列
        for (int r = 7; r <= lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }


            //科目1
            String firstLevelName = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue().trim();
            if (yijiKeMuMap.get(firstLevelName) == null) {
                return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 1 + "列,科目：" + firstLevelName + "不存在，请确保该一级科目填写正确，已经存在于科目列表中");
            }

            //科目2
            String secondLevelName = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue().trim();
            if (erjiKeMuMap.get(secondLevelName) == null) {
                return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 2 + "列,科目：" + secondLevelName + "不存在，请确保该二级科目已经存在于科目列表中");
            }

            //金额及校验
            Cell cell2 = row.getCell(2);
            ResponseData typeData = getDoubleValue(cell2);
            if (typeData.getCode() != 200) {
                return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 3 + "列,金额不是数字");
            }

            ValAndFormula jineAndFormula = (ValAndFormula) typeData.getData();
            double d = jineAndFormula.getValue();
            BigDecimal jine = cell2 == null ? BigDecimal.ZERO : new BigDecimal(d).setScale(2, BigDecimal.ROUND_HALF_UP);

            //备注
            Cell cell3 = row.getCell(3);
            String beizhu = cell3 == null ? "" : cell3.getStringCellValue();


            //插入数据
            ZxTzqk tzqk = new ZxTzqk();
            //门店
            tzqk.setBtyZxMdzxId(mdzxMap.get(dianMing));
            //科目2id
            tzqk.setSecondLevelId(erjiKeMuMap.get(secondLevelName));
            //科目2名称
            tzqk.setSecondLevelName(secondLevelName);
            //金额
            tzqk.setJine(jine);
            //金额对应的公式formula
            tzqk.setFormula(jineAndFormula.getFormula());
            //备注
            tzqk.setContent(beizhu);

            tzqkList.add(tzqk);
        }

        if (CollectionUtil.isNotEmpty(tzqkList)) {
            //插入新的投资情况之前，先删除已经存在的投资情况信息
            Long btyZxMdzxId = mdzxMap.get(dianMing);
            this.baseMapper.deleteByBtyZxMdzxId(btyZxMdzxId);

            for (ZxTzqk tzqk : tzqkList) {
                this.baseMapper.insert(tzqk);
            }
        }

        //重新计算一级合计，成本合计
        updateJineHeJi(mdzxMap.get(dianMing));


        return ResponseData.success();
    }


    /**
     * 重新计算一级合计，成本合计
     *
     * @param mdzxId
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateJineHeJi(Long mdzxId) {
        //获取所有的一级科目-全
        List<ZxTzqkViewResult> zxKemuResultList1 = zxKemuMapper.selectAllYijiKemu("1");
        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        //查找出当前店的所有投资情况记录
        ZxTzqkParam param = new ZxTzqkParam();
        param.setBtyZxMdzxId(mdzxId);
        List<ZxTzqkResult> zxTzqkResults = zxTzqkMapper.customList(param);
        //TODO-2 将所有的投资情况记录，放入二级科目中
        if (CollectionUtil.isNotEmpty(zxTzqkResults)) {
            for (ZxTzqkResult tzqkResult : zxTzqkResults) {
                if (tzqkResult.getFirstLevelId() != null && tzqkResult.getSecondLevelId() != null) {
                    //为二级科目放入指定内容
                    for (ZxTzqkViewResult erjiData : zxKemuResultList2) {
                        if (tzqkResult.getFirstLevelId() != null
                                && tzqkResult.getSecondLevelId() != null
                                && tzqkResult.getFirstLevelId().equals(erjiData.getFirstLevelId())
                                && tzqkResult.getSecondLevelId().equals(erjiData.getSecondLevelId())) {
                            erjiData.setJine(tzqkResult.getJine());
                            erjiData.setContent(tzqkResult.getContent());
                            erjiData.setBeiwang(tzqkResult.getBeiwang());
                        }
                    }
                } else {
                    //为一级科目放入金额
                    for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
                        if (tzqkResult.getFirstLevelId().equals(yijiData.getFirstLevelId())) {
                            yijiData.setJine(tzqkResult.getJine());
                        }

                    }
                }

            }
        }
        //成本合计
        BigDecimal sum = BigDecimal.ZERO;

        //TODO-3 在输出内容中加入一级科目汇总
        for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
            BigDecimal yijiHeji = BigDecimal.ZERO;
            for (ZxTzqkViewResult erjiData : zxKemuResultList2) {
                erjiData.setBtyZxMdzxId(mdzxId);
                if (erjiData.getFirstLevelId().equals(yijiData.getFirstLevelId())) {
                    erjiData.setSecondLevelName(erjiData.getName());
                    yijiHeji = yijiHeji.add(erjiData.getJine() == null ? BigDecimal.ZERO : erjiData.getJine());
                }
            }
            yijiData.setJine(yijiHeji);
            yijiData.setBtyZxMdzxId(mdzxId);
            sum = sum.add(yijiData.getJine() == null ? BigDecimal.ZERO : yijiData.getJine());
            yijiData.setFirstLevelName(yijiData.getName());
        }

        //更新一级合计记录-没有则创建
//        if (CollectionUtil.isNotEmpty(zxKemuResultList1)) {
//            for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
//                ZxTzqkParam param1 = new ZxTzqkParam();
//                param1.setBtyZxMdzxId(mdzxId);
//                param1.setFirstLevelId(yijiData.getFirstLevelId());
//                param1.setSecondLevelId(null);
//                List<ZxTzqkResult> zxTzqkResultList = zxTzqkMapper.selectTzqkByParam(param1);
//                //先删除所有-再添加
//                if (CollectionUtil.isNotEmpty(zxTzqkResultList)) {
//                    for (ZxTzqkResult zxTzqkResult : zxTzqkResultList) {
//                        this.removeById(zxTzqkResult.getBtyZxTzqkId());
//                    }
//                }
//                //创建-没有对应得一级科目
//                ZxTzqkParam param2 = new ZxTzqkParam();
//                param2.setBtyZxMdzxId(mdzxId);
//                param2.setFirstLevelId(yijiData.getFirstLevelId());
//                param2.setFirstLevelName(yijiData.getFirstLevelName());
//                param2.setJine(yijiData.getJine());
//                this.add(param2);
//            }
//        }

        //更新成本合计
        ZxTzqk zxTzqk = this.baseMapper.selectById(mdzxId);

        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setBtyZxMdzxId(mdzxId);
        mdzxParam.setChengbenHeji(sum);
        mdzxService.updateAndcalculateChengbenAndYusuan(mdzxParam);

    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateAllMdJineHeJi() {
        //获取所有的一级科目-全
        List<ZxTzqkViewResult> zxKemuResultListPillar1 = zxKemuMapper.selectAllYijiKemu("1");
        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultListPillar2 = zxKemuMapper.selectAllErjiKemu("2");
        ZxTzqkParam param = new ZxTzqkParam();
        List<ZxTzqkResult> allZxTzqkResults = zxTzqkMapper.customList(param);
        if (CollectionUtil.isNotEmpty(allZxTzqkResults)) {
            Map<Long, List<ZxTzqkResult>> allMdTzqkResultListMap = allZxTzqkResults.stream().collect(Collectors.groupingBy(ZxTzqkResult::getBtyZxMdzxId));
            //对所有门店都重新计算
            for (Map.Entry<Long, List<ZxTzqkResult>> thisMap : allMdTzqkResultListMap.entrySet()) {

                //获取所有的一级科目-全
                List<ZxTzqkViewResult> zxKemuResultList1 = new ArrayList<>();

                for (ZxTzqkViewResult result : zxKemuResultListPillar1) {
                    ZxTzqkViewResult zxTzqkViewResult = new ZxTzqkViewResult();
                    zxTzqkViewResult.setFirstLevelId(result.getFirstLevelId());
                    zxTzqkViewResult.setName(result.getName());
                    zxKemuResultList1.add(zxTzqkViewResult);
                }
                //获取所有的二级科目-全
                List<ZxTzqkViewResult> zxKemuResultList2 = new ArrayList<>();

                for (ZxTzqkViewResult result : zxKemuResultListPillar2) {
                    ZxTzqkViewResult zxTzqkViewResult = new ZxTzqkViewResult();
                    zxTzqkViewResult.setFirstLevelId(result.getFirstLevelId());
                    zxTzqkViewResult.setSecondLevelId(result.getSecondLevelId());
                    zxTzqkViewResult.setName(result.getName());
                    zxKemuResultList2.add(zxTzqkViewResult);
                }


                Long mdzxId = thisMap.getKey();
                //查找出当前店的所有投资情况记录
                List<ZxTzqkResult> zxTzqkResults = thisMap.getValue();
                //TODO-2 将所有的投资情况记录，放入二级科目中
                if (CollectionUtil.isNotEmpty(zxTzqkResults)) {
                    for (ZxTzqkResult tzqkResult : zxTzqkResults) {
                        if (tzqkResult.getFirstLevelId() != null && tzqkResult.getSecondLevelId() != null) {
                            //为二级科目放入指定内容
                            for (ZxTzqkViewResult erjiData : zxKemuResultList2) {
                                if (tzqkResult.getFirstLevelId() != null
                                        && tzqkResult.getSecondLevelId() != null
                                        && tzqkResult.getFirstLevelId().equals(erjiData.getFirstLevelId())
                                        && tzqkResult.getSecondLevelId().equals(erjiData.getSecondLevelId())) {
                                    erjiData.setJine(tzqkResult.getJine());
                                    erjiData.setContent(tzqkResult.getContent());
                                    erjiData.setBeiwang(tzqkResult.getBeiwang());
                                }
                            }
                        } else {
                            //为一级科目放入金额
                            for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
                                if (tzqkResult.getFirstLevelId().equals(yijiData.getFirstLevelId())) {
                                    yijiData.setJine(tzqkResult.getJine());
                                }

                            }
                        }

                    }
                }
                //成本合计-该店成本合计
                BigDecimal sum = BigDecimal.ZERO;

                //TODO-3 在输出内容中加入一级科目汇总
                for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
                    BigDecimal yijiHeji = BigDecimal.ZERO;
                    for (ZxTzqkViewResult erjiData : zxKemuResultList2) {
                        erjiData.setBtyZxMdzxId(mdzxId);
                        if (erjiData.getFirstLevelId().equals(yijiData.getFirstLevelId())) {
                            erjiData.setSecondLevelName(erjiData.getName());
                            yijiHeji = yijiHeji.add(erjiData.getJine() == null ? BigDecimal.ZERO : erjiData.getJine());
                        }
                    }
                    yijiData.setJine(yijiHeji);
                    yijiData.setBtyZxMdzxId(mdzxId);
                    sum = sum.add(yijiData.getJine() == null ? BigDecimal.ZERO : yijiData.getJine());
                    yijiData.setFirstLevelName(yijiData.getName());
                }

                //更新一级合计记录-没有则创建
//                if (CollectionUtil.isNotEmpty(zxKemuResultList1)) {
//                    for (ZxTzqkViewResult yijiData : zxKemuResultList1) {
//                        ZxTzqkParam param1 = new ZxTzqkParam();
//                        param1.setBtyZxMdzxId(mdzxId);
//                        param1.setFirstLevelId(yijiData.getFirstLevelId());
//                        param1.setSecondLevelId(null);
//                        List<ZxTzqkResult> zxTzqkResultList = zxTzqkMapper.selectTzqkByParam(param1);
//                        //先删除所有-再添加
//                        if (CollectionUtil.isNotEmpty(zxTzqkResultList)) {
//                            for (ZxTzqkResult zxTzqkResult : zxTzqkResultList) {
//                                this.removeById(zxTzqkResult.getBtyZxTzqkId());
//                            }
//                        }
//                        //创建-没有对应得一级科目
//                        ZxTzqkParam param2 = new ZxTzqkParam();
//                        param2.setBtyZxMdzxId(mdzxId);
//                        param2.setFirstLevelId(yijiData.getFirstLevelId());
//                        param2.setFirstLevelName(yijiData.getFirstLevelName());
//                        param2.setJine(yijiData.getJine());
//                        this.add(param2);
//                    }
//                }

                //更新成本合计
                //ZxTzqk zxTzqk = this.baseMapper.selectById(mdzxId);
                MdzxParam mdzxParam = new MdzxParam();
                mdzxParam.setBtyZxMdzxId(mdzxId);
                mdzxParam.setChengbenHeji(sum);
                mdzxService.updateAndcalculateChengbenAndYusuan(mdzxParam);
            }
        }
    }

    /**
     * 优化版一键获取--未写完
     *
     * @param paramsf
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResponseData obtainAllFromBaoxiaodanBatch(ZxTzqkViewResult paramsf) {
        //所有的二级科目
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        //所有的三级科目
        List<ZxTzqkViewResult> zxKemuResultList3 = zxKemuMapper.selectAllSanjiKemu("3");
        //所有的明细
        List<ZxChengbenMingxiResult> allMingxiList = zxChengbenMingxiService.selectAllMingXiAndKemuInfo();
        //按门店分组
        if (CollectionUtil.isNotEmpty(allMingxiList)) {
            Map<Long, List<ZxChengbenMingxiResult>> mdMingXiMap = allMingxiList.stream().collect(Collectors.groupingBy(ZxChengbenMingxiResult::getBtyZxMdzxId));
            //该门店所有明细
            List<ZxChengbenMingxiResult> thisMdMingXiList = mdMingXiMap.get(paramsf.getBtyZxMdzxId());
            if (CollectionUtil.isNotEmpty(thisMdMingXiList)) {
                Map<Long, List<ZxChengbenMingxiResult>> erJikemuXimgXiListMap = new HashMap<>();

                for (ZxChengbenMingxiResult mingxiResult : thisMdMingXiList) {
                    //2级科目明细
                    if ("2".equals(mingxiResult.getLevel())) {
                        if (erJikemuXimgXiListMap.get(mingxiResult.getBtyZxKemuId()) == null) {
                            //List<ZxChengbenMingxiResult> val

                        }
                    }
                    //3级科目明细
                    if ("3".equals(mingxiResult.getLevel())) {

                    }
                }


            }

        }


        for (ZxTzqkViewResult param : zxKemuResultList2) {
            //获取二级及当前二级下的所有三级的报销单，替换已有的投资情况备注数据
            ZxChengbenMingxiParam param1 = new ZxChengbenMingxiParam();
            param1.setBtyZxMdzxId(paramsf.getBtyZxMdzxId());
            param1.setBtyZxKemuId(param.getSecondLevelId());
            List<ZxChengbenMingxiResult> zxTzqkViewResults = zxChengbenMingxiService.selectAllMingXiUnderLevel(param1);
            //覆盖投资情况备注
            String content = "";
            BigDecimal sum = BigDecimal.ZERO;
            Map<String, String> contentMap = new HashMap<>();
            if (CollectionUtil.isNotEmpty(zxTzqkViewResults)) {
                for (ZxChengbenMingxiResult result : zxTzqkViewResults) {
                    String kemuJine = contentMap.get(result.getBtyZxKemuIdView());
                    if (kemuJine != null) {
                        String newJine = result.getJine() == null ? BigDecimal.ZERO.setScale(2).toString() : result.getJine().setScale(2).toString();
                        contentMap.put(result.getBtyZxKemuIdView(), kemuJine + "+" + newJine);
                    } else {
                        contentMap.put(result.getBtyZxKemuIdView(), result.getJine() == null ? BigDecimal.ZERO.setScale(2).toString() : result.getJine().setScale(2).toString());
                    }
                    sum = sum.add(result.getJine() == null ? BigDecimal.ZERO : result.getJine().setScale(2));
                }
                for (String key : contentMap.keySet()) {
                    String value = contentMap.get(key);
                    content = content + key + value + "，";
                }
            }


            //改变对应投资情况对应的备注
            ZxTzqkParam param2 = new ZxTzqkParam();
            param2.setBtyZxMdzxId(param.getBtyZxMdzxId());
            param2.setFirstLevelId(param.getFirstLevelId());
            param2.setSecondLevelId(param.getSecondLevelId());
            param2.setSecondLevelName(param.getSecondLevelName());
            param2.setContent(content);

            //覆盖金额
            param2.setJine(sum);
            this.editTzqk(param2);
        }
        return ResponseData.success();
    }


    @Override
    public ResponseData onClickobtainAll(ZxTzqkViewResult param) {
        //所有的二级科目
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        for (ZxTzqkViewResult result : zxKemuResultList2) {
            ZxTzqkViewResult param2 = new ZxTzqkViewResult();
            param2.setBtyZxMdzxId(param.getBtyZxMdzxId());
            param2.setFirstLevelId(result.getFirstLevelId());
            param2.setSecondLevelId(result.getSecondLevelId());
            this.obtainFromBaoxiaodan(param2);
        }

        return ResponseData.success();
    }

    @Override
    public ResponseData duizhangTongbu() {
        //获取不平的门店列表
        List<MdzxResult> list = this.baseMapper.queryMdListZhangbuping();
        for(MdzxResult r:list){
            System.out.println(r.toString());
            ZxTzqkViewResult param =new ZxTzqkViewResult();
            param.setBtyZxMdzxId(r.getBtyZxMdzxId());
            onClickobtainAll(param);
        }
        return ResponseData.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData obtainFromBaoxiaodan(ZxTzqkViewResult param) {
        System.out.println("投资情况获取报销单---------------------------begin");
        //获取二级及当前二级下的所有三级的报销单，替换已有的投资情况备注数据
        ZxChengbenMingxiParam param1 = new ZxChengbenMingxiParam();
        param1.setBtyZxMdzxId(param.getBtyZxMdzxId());
        param1.setBtyZxKemuId(param.getSecondLevelId());
        List<ZxChengbenMingxiResult> zxTzqkViewResults = zxChengbenMingxiService.selectAllMingXiUnderLevel(param1);
        //覆盖投资情况备注
        String content = "";
        BigDecimal sum = BigDecimal.ZERO;
        String gongshi = null;
        Map<String, String> contentMap = new HashMap<String, String>();
        Map<String, String> choudianMap = new HashMap<String, String>();
        if (CollectionUtil.isNotEmpty(zxTzqkViewResults)) {

            for (ZxChengbenMingxiResult result : zxTzqkViewResults) {
                if(result.getJine()==null){
                    continue;
                }
                String jine = result.getJine().setScale(2).toString();
                String qtkm = result.getQtkm();
                String key = result.getBtyZxKemuIdView();
                if (qtkm != null && !qtkm.trim().equals("")) {
                    if (qtkm.endsWith(jine)) {
                        key = qtkm.substring(0, qtkm.lastIndexOf(jine));
                    } else {
                        key = qtkm;
                    }
                }
                String kemuJine = contentMap.get(key);
                if (kemuJine != null) {
                    String newJine = result.getJine() == null ? BigDecimal.ZERO.setScale(2).toString() : result.getJine().setScale(2).toString();
                    contentMap.put(key, kemuJine + "+" + newJine);
                } else {
                    contentMap.put(key, result.getJine() == null ? BigDecimal.ZERO.setScale(2).toString() : result.getJine().setScale(2).toString());
                }

                //总金额
                sum = sum.add(result.getJine() == null ? BigDecimal.ZERO : result.getJine().setScale(2));

                //增加抽点
                String choudian = result.getCompanyView();
                if(choudian==null || choudian.equals("")){
                    choudian = "0.00";
                }
                BigDecimal choudian_b = new BigDecimal(choudian);
                System.out.println("choudian="+choudian);
                System.out.println("choudian_b="+choudian_b);
                if(choudian_b.doubleValue()>0){
                    sum=sum.add(choudian_b);
                    choudianMap.put(key,choudian);
                }

                //公式
                if (gongshi == null) {
                    gongshi = result.getJine().setScale(2).toString();
                    if(choudian_b.doubleValue()>0){
                        gongshi+=","+choudian_b.setScale(2).toString();
                    }
                } else {
                    if(choudian_b.doubleValue()>0){
                        gongshi += "," + result.getJine().setScale(2).toString()+","+choudian_b.setScale(2).toString();;
                    }else {
                        gongshi += "," + result.getJine().setScale(2).toString();
                    }
                }
            }


            for (String key : contentMap.keySet()) {
                String value = contentMap.get(key);
                boolean isAddJe = true;
                if (key.indexOf("，") > -1 || isContainNumber(key)) {
                    isAddJe = false;
                }
                if ("".equals(content)) {
                    if (isAddJe) {
                        content = key + value;
                        if(choudianMap.containsKey(key)){
                            content+=" 抽点"+choudianMap.get(key);
                        }
                    } else {
                        content = key;
                        if(choudianMap.containsKey(key)){
                            content+=" 抽点"+choudianMap.get(key);
                        }
                    }
                } else {
                    if (isAddJe) {
                        content = content + "，" + key + value;
                        if(choudianMap.containsKey(key)){
                            content+=" 抽点"+choudianMap.get(key);
                        }
                    } else {
                        content = content + "，" + key;
                        if(choudianMap.containsKey(key)){
                            content+=" 抽点"+choudianMap.get(key);
                        }
                    }
                }
            }
        }


        //改变对应投资情况对应的备注
        ZxTzqkParam param2 = new ZxTzqkParam();
        param2.setBtyZxMdzxId(param.getBtyZxMdzxId());
        param2.setFirstLevelId(param.getFirstLevelId());
        param2.setSecondLevelId(param.getSecondLevelId());
        param2.setSecondLevelName(param.getSecondLevelName());
        param2.setContent(content);
        System.out.println(content);

        //覆盖金额
        param2.setJine(sum);
        param2.setFormula(gongshi);
        this.editTzqk(param2);

        return ResponseData.success();
    }

    public static boolean isContainNumber(String company) {

        Pattern p = Pattern.compile("[0-9]");
        Matcher m = p.matcher(company);
        if (m.find()) {
            return true;
        }
        return false;
    }

    /**
     * 多次处理单个二级科目的一键获取
     *
     * @param param
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData obtainAllFromBaoxiaodan(ZxTzqkViewResult param) {
        List<ZxTzqkViewResult> zxKemuResultList = zxKemuMapper.selectAllErjiKemu("2");
        for (ZxTzqkViewResult result : zxKemuResultList) {
            ZxTzqkViewResult result1 = new ZxTzqkViewResult();
            result1.setBtyZxMdzxId(param.getBtyZxMdzxId());
            result1.setFirstLevelId(result.getFirstLevelId());
            result1.setSecondLevelId(result.getSecondLevelId());
            obtainFromBaoxiaodan(result1);
        }
        return ResponseData.success();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData batchImport_md_tzqk(String fileName, MultipartFile file) throws Exception {
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = getWorkBook(is);
        Sheet sheet = wb.getSheetAt(0);
        ResponseData responseData = mdtzqkHander(sheet);
        return responseData;
    }

    /**
     * 投资情况sheet处理
     *
     * @param sheet
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData mdtzqkHander(Sheet sheet) {
        //获取所有的门店装修的门店信息
        MdzxParam mdzxParam = new MdzxParam();
        mdzxParam.setContainsCopy("no");
        List<MdzxResult> mdzxResultList = mdzxService.customList(mdzxParam);
        Map<String, Long> mdzxMap = mdzxResultList.stream().collect(Collectors.toMap(MdzxResult::getBtyBrandIdView, MdzxResult::getBtyZxMdzxId));

        List<ZxTzqk> tzqkList = new ArrayList<>();

        //校验店是否存在
        Row rowFirst = sheet.getRow(2);
        String dianMing = rowFirst.getCell(1).getStringCellValue().trim();

        if (ToolUtil.isEmpty(dianMing)) {
            return new ErrorResponseData("门店信息不允许为空");
        }

        Long md_id = null;
        //工程项目中已经存在，门店装修中不存在，自动创建门店装修信息
        if (mdzxMap.get(dianMing) == null) {
            Mdzx mdzx = new Mdzx();
            mdzx.setBtyBrandIdView(dianMing);
            mdzx.setCreateTime(new DateTime());
            mdzx.setCreateUser(1L);
            mdzx.setCreateUserName("admin");
            mdzx.setDelFlag(0);
            mdzxService.save(mdzx);
            md_id = mdzx.getBtyZxMdzxId();
        } else {
            md_id = mdzxMap.get(dianMing);
        }

        //获取所有的一级科目-全
        List<ZxTzqkViewResult> zxKemuResultList1 = zxKemuMapper.selectAllYijiKemu("1");
        Map<String, Long> yijiKeMuMap = zxKemuResultList1.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getFirstLevelId));
        //获取所有的二级科目-全
        List<ZxTzqkViewResult> zxKemuResultList2 = zxKemuMapper.selectAllErjiKemu("2");
        Map<String, Long> erjiKeMuMap = zxKemuResultList2.stream().collect(Collectors.toMap(ZxTzqkViewResult::getName, ZxTzqkViewResult::getSecondLevelId));


        int lastRowNum = sheet.getLastRowNum();

        Long lastKmId = null;
        String lastKmName = null;
        //循环列
        for (int r = 7; r <= lastRowNum; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }


            //科目1
            String firstLevelName = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue().trim();
            if ("总计".equals(firstLevelName)) {
                break;
            }
            Long firstLevelIdCur = null;
            String firstLevelNameCur = null;
            if ("".equals(firstLevelName.trim()) || "　".equals(firstLevelName)) {
                firstLevelIdCur = lastKmId;
                firstLevelNameCur = lastKmName;
            } else {
                if (yijiKeMuMap.get(firstLevelName) == null) {
                    return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 1 + "列,科目：" + firstLevelName + "不存在，请确保该一级科目填写正确，已经存在于科目列表中");
                }
                lastKmId = yijiKeMuMap.get(firstLevelName);
                lastKmName = firstLevelName;
                continue;
            }
            //科目2
            String secondLevelName = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue().trim();
            if (erjiKeMuMap.get(secondLevelName) == null) {
                return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 2 + "列,科目：" + secondLevelName + "不存在，请确保该二级科目已经存在于科目列表中");
            }

            //金额及校验
            Cell cell2 = row.getCell(2);
            ResponseData typeData = getDoubleValue(cell2);
            if (typeData.getCode() != 200) {
                return new ErrorResponseData("导入失败，第" + (r + 1) + "行" + 3 + "列,金额不是数字");
            }
            ValAndFormula jineAndFormula = (ValAndFormula) typeData.getData();
            double d = jineAndFormula.getValue();
            System.out.println("金额=" + d);
            BigDecimal jine = cell2 == null ? BigDecimal.ZERO : new BigDecimal(d).setScale(4, BigDecimal.ROUND_HALF_UP);
            System.out.println("金额Jine=" + jine);

            //备注
            Cell cell3 = row.getCell(3);
            String beizhu = cell3 == null ? "" : cell3.getStringCellValue();


            //插入数据
            ZxTzqk tzqk = new ZxTzqk();
            //门店
            tzqk.setBtyZxMdzxId(md_id);
            //科目2id
            tzqk.setSecondLevelId(erjiKeMuMap.get(secondLevelName));
            //科目2名称
            tzqk.setSecondLevelName(secondLevelName);
            //金额
            tzqk.setJine(jine);
            //公式
            tzqk.setFormula(jineAndFormula.getFormula());
            //备注
            tzqk.setContent(beizhu);

            tzqkList.add(tzqk);
        }

        if (CollectionUtil.isNotEmpty(tzqkList)) {
            //插入新的投资情况之前，先删除已经存在的投资情况信息
            Long btyZxMdzxId = md_id;
            this.baseMapper.deleteByBtyZxMdzxId(btyZxMdzxId);

            for (ZxTzqk tzqk : tzqkList) {
                this.baseMapper.insert(tzqk);
            }
        }

        //重新计算一级合计，成本合计
        updateJineHeJi(md_id);
        return ResponseData.success(md_id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResponseData importMdTzCb(MultipartFile file) throws Exception {
        String fname = file.getOriginalFilename();
        if (!fname.matches("^.+\\.(?i)(xls)$") && !fname.matches("^.+\\.(?i)(xlsx)$")) {
            return new ErrorResponseData("上传文件格式不正确");
        }
        Workbook workBook = ExcelUtil.getWorkBook(file);

        Sheet firstsheet = workBook.getSheetAt(0);
        Sheet yusuansheet = workBook.getSheetAt(1);

        //处理sheet1 投资情况
        ResponseData rs1 = mdtzqkHander(firstsheet);
        if (rs1.getCode() != ResponseData.DEFAULT_SUCCESS_CODE) {
            return rs1;
        }
        Object data = rs1.getData();
        if (ToolUtil.isNotEmpty(data)) {
            Long mdid = Long.valueOf(data.toString());// 第2个sheet门店名不做判断， 以第一个sheet的门店为准
            //处理sheet2 预算数据
            ResponseData rs2 = zxyusuanService.yusuanSheet(yusuansheet, mdid);
            if (rs2.getCode() == ResponseData.DEFAULT_SUCCESS_CODE) {
                return SuccessResponseData.success("导入成功");
            } else {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return rs2;
            }
        }
        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ErrorResponseData("导入失败");
    }


    @Override
    public ResponseData batchImportMdTzCb() throws Exception {
        String path = "D:/data/mendianTouziYsuan/";
        File dir = new File(path);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        File[] files = dir.listFiles();
        List<String> logs = new ArrayList<>();
        for (File f : files) {
            String fname = f.getName();
            if (!fname.matches("^.+\\.(?i)(xls)$") && !fname.matches("^.+\\.(?i)(xlsx)$")) {
                continue;
            }
            ResponseData handFile = transService.handFile(f);
            if (handFile.getCode() == ResponseData.DEFAULT_SUCCESS_CODE) {
                logs.add("'" + fname + "', 导入成功");
            } else {
                logs.add("'" + fname + "', " + handFile.getMessage());
            }
        }
        FileUtils.write2File(path, "mdtzys-" + DateUtil.getStampNow() + ".txt", logs);
        return SuccessResponseData.success();
    }


    private Serializable getKey(ZxTzqkParam param) {
        return param.getBtyZxTzqkId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private ZxTzqk getOldEntity(ZxTzqkParam param) {
        return this.getById(getKey(param));
    }

    public ZxTzqk getEntity(ZxTzqkParam param) {
        ZxTzqk entity = new ZxTzqk();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }


    private ResponseData getDoubleValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData(new Double(0));
        }

        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            //数值型
            double numericCellValue = cell.getNumericCellValue();
            ValAndFormula valAndFormula = new ValAndFormula();
            valAndFormula.setValue(numericCellValue);
            valAndFormula.setFormula(cell.toString());
            return new SuccessResponseData(valAndFormula);
        } else if (cellType == CellType.BLANK) {
            //空值型
            ValAndFormula valAndFormula = new ValAndFormula();
            valAndFormula.setValue(new Double(0));
            valAndFormula.setFormula(cell.toString());
            return new SuccessResponseData(valAndFormula);
        } else if (cellType == CellType.FORMULA) {
            //公式型
            ValAndFormula valAndFormula = new ValAndFormula();
            valAndFormula.setFormula(cell.toString());
            cell.setCellType(CellType.STRING);
            String str = cell.getStringCellValue();
            valAndFormula.setValue(Double.parseDouble(str));
            return new SuccessResponseData(valAndFormula);
        } else {
            //字符串型
            String str = cell.getStringCellValue();
            if(str.trim().equals("")){
                str="0";
            }
            try {
                double value = Double.parseDouble(str);
                ValAndFormula valAndFormula = new ValAndFormula();
                valAndFormula.setValue(value);
                valAndFormula.setFormula(cell.toString());
                return new SuccessResponseData(valAndFormula);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("不是数字");
        }
    }

    private ResponseData getIntegerValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData(new Integer(0));
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            //数值型
            Double numericCellValue = cell.getNumericCellValue();

            int valInt = Double.valueOf(numericCellValue).intValue();
            return new SuccessResponseData(valInt);
        } else if (cellType == CellType.BLANK) {
            //空值型
            return new SuccessResponseData(new Integer(0));
        } else if (cellType == CellType.FORMULA) {
            //公式型
            cell.setCellType(CellType.STRING);
            String str = cell.getStringCellValue();
            return new SuccessResponseData(Integer.parseInt(str));
        } else {
            //字符串型
            String str = cell.getStringCellValue();
            if(str.trim().equals("")){
                str="0";
            }
            try {
                double value = Double.parseDouble(str);
                int valInt = Double.valueOf(value).intValue();
                return new SuccessResponseData(valInt);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("不是数字");
        }
    }

    private ResponseData getStringValue(Cell cell) {
        if (cell == null) {
            return new SuccessResponseData("");
        }

        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC) {
            Double numericCellValue = cell.getNumericCellValue();
            return new SuccessResponseData(numericCellValue.toString());
        } else if (cellType == CellType.BLANK) {
            //空值型
            return new SuccessResponseData("");
        } else {
            String str = cell.getStringCellValue();
            try {
                return new SuccessResponseData(str);
            } catch (NumberFormatException ex) {
            }
            return new ErrorResponseData("解析失败");
        }
    }

    public static void main(String[] args) {
//        Object sstr = 199579.1;
//        double d = (double)sstr;
//        BigDecimal Jine = new BigDecimal(d).setScale(2, BigDecimal.ROUND_HALF_UP);
//        System.out.println(sstr);

        String qtkm = "发光字222.22";
        String jine = "222.22";
        System.out.println(qtkm.lastIndexOf(jine));
        System.out.println(qtkm.endsWith(jine));
        System.out.println(qtkm.substring(0, 3));
    }

    @Data
    private class ValAndFormula {
        private Double value;
        private String formula;
    }

}
